package com.english.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.english.entity.StudentClassHours;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * 学生班级课时Mapper
 *
 * @author wlq
 * @since 2025-08-06
 */
@Mapper
public interface StudentClassHoursMapper extends BaseMapper<StudentClassHours> {

    /**
     * 获取学生的所有班级课时信息
     */
    @Select("SELECT sch.*, cc.class_name, cc.class_type, u.name as student_name " +
            "FROM student_class_hours sch " +
            "LEFT JOIN course_class cc ON sch.class_id = cc.id " +
            "LEFT JOIN user u ON sch.student_id = u.id " +
            "WHERE sch.student_id = #{studentId} " +
            "ORDER BY cc.id")
    List<StudentClassHours> selectByStudentIdWithDetails(@Param("studentId") Long studentId);

    /**
     * 获取班级的所有学生课时信息
     */
    @Select("SELECT sch.*, u.name as student_name, cc.class_name, cc.class_type " +
            "FROM student_class_hours sch " +
            "LEFT JOIN user u ON sch.student_id = u.id " +
            "LEFT JOIN course_class cc ON sch.class_id = cc.id " +
            "WHERE sch.class_id = #{classId} " +
            "ORDER BY u.name")
    List<StudentClassHours> selectByClassIdWithDetails(@Param("classId") Long classId);

    /**
     * 获取学生在特定班级的课时信息
     */
    @Select("SELECT sch.*, cc.class_name, cc.class_type, u.name as student_name " +
            "FROM student_class_hours sch " +
            "LEFT JOIN course_class cc ON sch.class_id = cc.id " +
            "LEFT JOIN user u ON sch.student_id = u.id " +
            "WHERE sch.student_id = #{studentId} AND sch.class_id = #{classId}")
    StudentClassHours selectByStudentAndClass(@Param("studentId") Long studentId, @Param("classId") Long classId);

    /**
     * 更新学生班级课时
     */
    @Update("UPDATE student_class_hours SET " +
            "total_hours = #{totalHours}, " +
            "used_hours = #{usedHours}, " +
            "remaining_hours = #{remainingHours}, " +
            "update_time = NOW() " +
            "WHERE student_id = #{studentId} AND class_id = #{classId}")
    int updateHours(@Param("studentId") Long studentId, 
                   @Param("classId") Long classId,
                   @Param("totalHours") Integer totalHours,
                   @Param("usedHours") Integer usedHours,
                   @Param("remainingHours") Integer remainingHours);

    /**
     * 获取学生总剩余课时
     */
    @Select("SELECT COALESCE(SUM(remaining_hours), 0) FROM student_class_hours WHERE student_id = #{studentId}")
    Integer selectTotalRemainingHours(@Param("studentId") Long studentId);
} 